sql 序号生成方法

1.rank()分析函数

语法: RANK() OVER([PARTITION BY expr1[,expr2,..,exprn]] ORDER BY expr1[,expr2,..,exprn])

   功能:                                                                                                                                            先将记录按PARTITION分组,组内再以ORDER BY排序,算出当前记录在组内所处的级别(名次). PRATITION条件省略时,表明不分组,或将全录记录作为一组

例子: 有表如下:

SQL> select * from test;

NAME       SUBJECT         SCORE ---------- ---------- ---------- aa         chinese            90 aa         art                     88 aa         english            80 bb         chinese            80 bb         art                     92 bb         english            96 cc         chinese            73 cc         art                      78 cc         english            60

已选择9行。

执行语句如下:

SQL> break on name skip 1; SQL> select * from ( select name ,subject,score ,rank() over ( partition by name order by score desc)  score_rank  from  test)  where score_rank select * from (select name ,subject,score ,rank() over ( partition by subject order by score desc) score_rank from test) where score_rank insert into test values('dd','chinese',80);

已创建 1 行。

SQL> insert into test values('dd','art',78);

已创建 1 行。

SQL> insert into test values('dd','english',96);

已创建 1 行。

SQL> select * from (select name ,subject,score,dense_rank() over ( partition by subject order by score desc) score_rank from test) where score_rank  select * from (select name ,subject,score ,dense_rank() over ( partition by name order by score desc) score_rank from test) where score_rank select * from (select name ,subject,score,rank() over ( partition by name order by score desc) score_rank from test) where score_rank select * from (select name ,subject,score,rank() over ( partition by subject order by score desc) score_rank from test) where score_rank